ShalaMaster

Import data using Import set and Transform map

Import set & Transform Map

Whenever you want to load data from any external sources into service now, you can use import set and transform map.

Import Set :

Import set is powerful tool used to import data from external application and then map that data into service-now table.

Transform Map :

Transform map is responsible for determining relationship between fields of import set table and service-now table.

During transformation, data is copied from the Import Set table to the destination table using transform map.

Example - To load data into service now table, we need to understand table structure of that target table first.

Observations -

This table does not contain any mandatory fields.
Contains reference field for Owned by , Approval group, support group, managed by, SLA, location etc.
Contains choice field for Business criticality, used for, operational status, service classification etc.

For reference field and choice field we need to specify appropriate choice action.

Steps to load data -

Export sample data from existing table

Go to load data from left navigation

Create import set table
Add label – import_services
Choose a file to upload - cmdb_ci_service.xlsx
Sheet number : 1 (sheet number of a excel file)
Header row : 1 (row number on which table fields are present)

Once import set created it will redirect you to following screen

Now we need to create transform map

Field mapping -

Field mappings are rules that are used to map the fields of stagging or import set table with target table fields.
Field maps establish a relationship between a field in an import set table and a field in the target table.

Mapping assist -

To map the fields of import set table with target table manually, click the Mapping Assist Related Link.

Auto Map:

Click the Auto Map Matching Fields Related Link for ServiceNow to attempt to match the staging table columns to the target table columns based on column name. The auto mapped fields appear in the Field Maps section (tab).

Coalesce Value:

Coalesce is property of field mapping , which will be deciding parameter for inserting a new record or updating the existing record in target table
The coalesce is set on the field or multiple fields which will be considered as unique key in target table.
If match is found the existing record is updated .
If match is not found then new record is inserted into target table.

No coalesce :

If no coalesce is defined, all imported rows are treated as new records.
No existing records are updated.

Here setting coalesce value on name field.

Choice Action :

Choice Action is for Reference field and choice field only. It has three choices i.e. create, reject, Ignore. By defult choice action is create. It is for unknown data transfer. We can set choice action in field maps in transform history.

Case1- Choice Action is Create:

In this case if unknown data is entered then the data value is created in respective reference field or choice field and data is transferred in Target table.

Case2- Choice Action is ignore:

In this case if unknown data is entered then the data value is ignored in respective reference field or choice field and there will be emplty value get transfered (or previous value remains unchanged) in Target table.

Case3- Choice Action is reject:

In this case if unknown data is entered the whole record is rejected and no data transfer in Target table.

Set choice action for reference and choice fields

Transform map is ready to transform data from import set to target table

Check the transform history

Check import set table

Now check the main table - data is loaded

Transformation Script :

A transform script allows you to apply extra business logic on a specified stage of the transformation.
Transform map allow a script to run as a event occur while import set being transformed into service-now table.

There are three types of transform map script :

Transform Script - that modify the processing of events at different stages of a transformation

onStart : The onStart event script is processed at the start of an import run, before any data rows are read.

onComplete : The onComplete event script is processed at the end of an import run, after all data rows are read and transformed.

onBefore : The onBefore event script is processed at the start of a row transformation, before the source row is transformed into the target row.

Example - Load the companies into watch list field, and check if the company already exist, if yes then skip that company if no add it.

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {

var company_list = target.getDisplayValue("u_companies");

if (company_list != "") {

if (source.u_companies != "") {

if (company_list.indexOf(source.u_companies) == -1) {

company_list = company_list + "," + source.u_companies;

source.u_companies = company_list.toString();

} else {

source.u_companies = "";

}

} else {

source.u_companies = "";

}

} else {

gs.info("compnay list empty ");

}

})(source, map, log, target);

onAfter : The onAfter event script is processed at the end of a row transformation, after the source row has been transformed into the target row and saved.

onChoiceCreate : The onChoiceCreate event script is processed at the start of a choice value creation, before the new choice value is created.

onForeignInsert : The onForeignInsert event script is processed at the start of the creation of a related, referenced record, before the record is created

Use Source script- Open the name field map from transform map, and update the script as follows,

Make changes in excel sheet for the name column and reload the data. and run the transform map.

New service is created with prefix as Coming.

Run script -This is explicit Transform Map scripts, which explicitly define mapping relationships in the Transform Map record itself. It runs after the source field values have been copied over to the target record, and before they are written to the database.
This can be used to hard code some target field values as shown in below script